import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

public class projectServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
  protected String database = "";
  protected String port = "";
  protected String username = "";
 	protected String password = "";

	public projectServlet() {
     parseXML("/Users/emperor1412/Desktop/ServerConfig.xml");
	}
	public boolean ExecuteQuery(String sql)
	{
		boolean result = true;
		try
		{
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://localhost:"+ (port.equalsIgnoreCase("") ? "5432" : port ) +"/"+ (database.equalsIgnoreCase("") ? "buzzcomm" : database ) ;
			Properties props = new Properties();
			props.setProperty("user", username.equalsIgnoreCase("") ? "postgres" : username );
			props.setProperty("password",password.equalsIgnoreCase("") ? "123456" : password );
			Connection connection = DriverManager.getConnection(url, props);
			Statement stmt = connection.createStatement();
			result = stmt.execute(sql);
			stmt.close();
			connection.close();
		}
		catch(ClassNotFoundException ex){
			ex.printStackTrace();
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		return result;
	}

protected void parseXML(String filename) {
	try {
		System.out.println("filename : " + filename);
		File file = new File(filename);
      if (file.exists()) {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.parse(filename);
        NodeList list = doc.getElementsByTagName("*");
        for (int i = 0; i < list.getLength(); i++) {
          Element element = (Element) list.item(i);				
          System.out.println(element.getNodeName()+" : "+element.getTextContent());
          if (element.getNodeName().equalsIgnoreCase("database")) {
            database = element.getTextContent(); 
          } else if (element.getNodeName().equalsIgnoreCase("port")) {
            port = element.getTextContent();
          } else if (element.getNodeName().equalsIgnoreCase("username")) {
            username = element.getTextContent();
          } else if (element.getNodeName().equalsIgnoreCase("password")) {
            password = element.getTextContent();
        }
      }
    } else {
        System.out.print("File not found!");
    }
  } catch (Exception e) {
      System.exit(1);
  }
}
protected void insertRecord( String name, String code, String description, String id_type, String pm, String is_template, String status, String plan_start_date, String plan_end_date, String real_start_date, String real_end_date, String creator, String deleted, String id_company ){
			String query = "select sp_project_insert(character varying(255) '" + name + "'"  +", character varying(20) '" + code + "'"  +", character varying '" + description + "'"  +", integer '" + id_type + "'"  +", integer '" + pm + "'"  +", boolean '" + is_template + "'"  +", integer '" + status + "'"  +", timestamp without time zone '" + plan_start_date + "'"  +", timestamp without time zone '" + plan_end_date + "'"  +", timestamp without time zone '" + real_start_date + "'"  +", timestamp without time zone '" + real_end_date + "'"  +", integer '" + creator + "'"  +", boolean '" + deleted + "'"  +", integer '" + id_company + "'" +");";
			ExecuteQuery(query);
}
	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		HttpSession session = request.getSession(true);
		try{
			String name = request.getParameter("name");
			String code = request.getParameter("code");
			String description = request.getParameter("description");
			String id_type = request.getParameter("id_type");
			String pm = request.getParameter("pm");
			String is_template = request.getParameter("is_template");
			String status = request.getParameter("status");
			String plan_start_date = request.getParameter("plan_start_date");
			String plan_end_date = request.getParameter("plan_end_date");
			String real_start_date = request.getParameter("real_start_date");
			String real_end_date = request.getParameter("real_end_date");
			String creator = request.getParameter("creator");
			String deleted = request.getParameter("deleted");
			String id_company = request.getParameter("id_company");

 		insertRecord( name, code, description, id_type, pm, is_template, status, plan_start_date, plan_end_date, real_start_date, real_end_date, creator, deleted, id_company );
		}
		catch (Exception ex){
			System.out.println("there is error");
			ex.printStackTrace();
		}
		finally{
			out.close();
		}
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

}
